﻿using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;

namespace Pe.Petecos.Comercial.Cross.Document.Excel
{
    /// <summary>
    /// Objeto que permite manipular un archivo excel
    /// </summary>
    /// <remarks>
    /// Creacion: 	JLRR 20140514 <br />
    /// Referencias: <br/>
    /// Introducing the Office (2007) Open XML File Formats: http://msdn.microsoft.com/en-us/library/aa338205.aspx
    /// Parsing and Reading Large Excel Files with the Open XML SDK: http://blogs.msdn.com/b/brian_jones/archive/2010/05/27/parsing-and-reading-large-excel-files-with-the-open-xml-sdk.aspx
    /// Creating Documents by Using the Open XML Format SDK : http://msdn.microsoft.com/en-us/library/dd440953(v=office.12)
    /// Advanced styling in Excel Open XML: http://polymathprogrammer.com/2009/12/21/advanced-styling-in-excel-open-xml/
    /// Leer tablas de un Excel con Open XML y Linq: http://www.returngis.net/2010/12/leer-tablas-de-un-excel-con-open-xml-y-linq/
    /// Crear un documento Excel con Open XML: http://www.returngis.net/2010/04/crear-un-documento-excel-con-open-xml/
    /// </remarks>
    public class ExcelFileManager
    {
        /// <summary>
        /// Ruta del archivo
        /// </summary>
        public string Filepath { get; set; }
        /// <summary>
        /// Archivos tream
        /// </summary>
        public Stream FileStream { get; set; }
        /// <summary>
        /// Ignorar Cabecera de Fila
        /// </summary>
        public bool IgnoreHeaderRow { get; set; }
        /// <summary>
        /// Estilo
        /// </summary>
        public List<Style> Styles { get; set; }
        /// <summary>
        /// Formatos de numeración
        /// </summary>
        public NumberingFormats numberingFormats { get; set; }

        /// <summary>
        /// Siguiente índice de cadenas compartidas
        /// </summary>
        public int nextIndexSharedString;
        /// <summary>
        /// Cadena de elementos Índice de mapas
        /// </summary>
        public Hashtable stringItemIndexMap;

        /// <summary>
        /// Administrador de archivo Excel
        /// </summary>
        /// <param name="numberingFormats">Formatos de numeración</param>
        public ExcelFileManager(NumberingFormats numberingFormats = null)
        {
            this.numberingFormats = numberingFormats;
        }

        /// <summary>
        /// Administrador de archivo Excel
        /// </summary>
        /// <param name="filePath">Ruta del archivo</param>
        /// <param name="numberingFormats">Formatos de numeración</param>
        public ExcelFileManager(string filePath, NumberingFormats numberingFormats = null)
        {
            this.Filepath = filePath;
            this.numberingFormats = numberingFormats;
        }

        /// <summary>
        /// Administrador de archivo Excel
        /// </summary>
        /// <param name="fileStream">Archivo Corriente</param>
        /// <param name="numberingFormats">Formatos de numeración</param>
        public ExcelFileManager(Stream fileStream, NumberingFormats numberingFormats = null)
        {
            this.FileStream = fileStream;
            this.numberingFormats = numberingFormats;
        }

        /// <summary>
        /// Administrador de archivo Excel
        /// </summary>
        /// <param name="filePath">Ruta del archivo</param>
        /// <param name="ignoreHeaderRow">Ignorar fila de cabecera</param>
        /// <param name="numberingFormats">Formatos de numeración</param>
        public ExcelFileManager(string filePath, bool ignoreHeaderRow, NumberingFormats numberingFormats = null)
        {
            this.Filepath = filePath;
            this.IgnoreHeaderRow = ignoreHeaderRow;
            this.numberingFormats = numberingFormats;
        }

        /// <summary>
        /// Administrador de archivo Excel
        /// </summary>
        /// <param name="fileStream">Archivo Corriente</param>
        /// <param name="ignoreHeaderRow">Ignorar fila de cabecera</param>
        /// <param name="numberingFormats">Formatos de numeración</param>
        public ExcelFileManager(Stream fileStream, bool ignoreHeaderRow, NumberingFormats numberingFormats = null)
        {
            this.FileStream = fileStream;
            this.IgnoreHeaderRow = ignoreHeaderRow;
            this.numberingFormats = numberingFormats;
        }

        /// <summary>
        /// verifica si el documento esta abierto
        /// </summary>
        /// <returns>Bool: devuelve true si esta abierto y false si no lo está</returns>
        public bool LockedDocument()
        {
            try
            {
                FileStream = new FileStream(this.Filepath, FileMode.Open, FileAccess.Read, FileShare.Read);
                return false;
            }
            catch (Exception)
            {
                return true;
            }
        }
        /// <summary>
        /// Permite aperturar el documento excel
        /// </summary>
        /// <returns>Abrir Documento</returns>
        private SpreadsheetDocument OpenDocument()
        {
            return (this.FileStream == null) ? SpreadsheetDocument.Open(this.Filepath, false) : SpreadsheetDocument.Open(this.FileStream, false);
        }
        /// <summary>
        /// obtiene todas las hojas y su contenido de un documento excel
        /// </summary>
        /// <returns>List : lista de hojas que contiene el documento</returns>
        public List<WorkSheet> ReadDocument()
        {
            List<WorkSheet> excelDocument = new List<WorkSheet>();
            using (var document = OpenDocument())
            {
                var workbook = document.WorkbookPart.Workbook;

                if (document.WorkbookPart.SharedStringTablePart != null)
                {
                    var sharedStringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
                    var workbookStylesPart = document.WorkbookPart.WorkbookStylesPart;
                    var stylesheet = workbookStylesPart != null ? workbookStylesPart.Stylesheet : null;
                    var cellFormats = stylesheet != null ? stylesheet.CellFormats : null;

                    var workSheets = from work in workbook.Descendants<Sheet>() select new WorkSheet() { Id = work.Id, Name = work.Name };

                    int workSheetIndex = 1;
                    foreach (WorkSheet sheet in workSheets)
                    {
                        var worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id);
                        sheet.DefinedNameValues = this.BuildDefinedNamesTable(document.WorkbookPart, sheet.Name);

                        if (!this.IgnoreHeaderRow)
                        {
                            sheet.HeaderRow = (from row in worksheetPart.Worksheet.Descendants<Row>()
                                               where row.RowIndex == 1
                                               select new RowData(row, sharedStringTable, cellFormats)).First(r => r.Cells != null && r.Cells.Count > 0);
                        }

                        sheet.Rows = GetRows(worksheetPart, sharedStringTable, cellFormats).Where(r => r.Cells != null && r.Cells.Count > 0).ToList();
                        sheet.WorkSheetIndex = workSheetIndex++;
                        excelDocument.Add(sheet);

                    }
                }
            }
            this.FileStream.Close();
            return excelDocument;
        }
        /// <summary>
        /// Contruye la tabla de definición de nombres del excel
        /// </summary>
        /// <param name="workbookPart">Libro Excel OPEN XML</param>
        /// <param name="sheetNameFind">Nombre de la hoja a buscar</param>
        /// <returns>Valores nombre definido</returns>
        List<DefinedNameVal> BuildDefinedNamesTable(WorkbookPart workbookPart, string sheetNameFind)
        {
            //Build a list
            List<DefinedNameVal> definedNameValues = new List<DefinedNameVal>();

            var tableName = workbookPart.Workbook.GetFirstChild<DefinedNames>();

            if (tableName != null)
            {

                foreach (DefinedName name in workbookPart.Workbook.GetFirstChild<DefinedNames>())
                {
                    //Parse defined name string...
                    string key = name.Name;
                    string reference = name.InnerText;

                    string sheetName = reference.Split('!')[0];
                    sheetName = sheetName.Trim('\'');

                    if (sheetName == sheetNameFind && name.Hidden == null && !reference.Contains("REF"))
                    {
                        //Assumption: None of my defined names are relative defined names (i.e. A1)
                        string range = reference.Split('!')[1];

                        string[] rangePart = range.Split(':');

                        string[] rangePartArray = rangePart[0].Split('$');

                        string startCol = rangePartArray[1];
                        int startRow = rangePartArray.Length == 3 ? Convert.ToInt32(rangePartArray[2].Trim()) : 0;

                        string endCol = startCol;
                        int endRow = 0;

                        if (rangePart.Length == 2)
                        {
                            rangePartArray = rangePart[1].Split('$');
                            endCol = rangePartArray[1];
                            endRow = rangePartArray.Length == 3 ? Convert.ToInt32(rangePartArray[2].Trim()) : 999999;
                        }

                        definedNameValues.Add(new DefinedNameVal() { Key = key, SheetName = sheetName, StartColumn = startCol, StartRow = startRow, EndColumn = endCol, EndRow = endRow });
                    }

                }
            }

            return definedNameValues;
        }

        /// <summary>
        /// obtiene todas las hojas y su contenido de un documento excel
        /// </summary>
        /// <param name="workSheetName">nombre de la hoja a obtener</param>
        /// <returns>List : lista de filas que contiene la hoja</returns>
        public IEnumerable<RowData> ReadWorkSheet(string workSheetName)
        {
            using (var document = OpenDocument())
            {
                var workbook = document.WorkbookPart.Workbook;
                var sharedStringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
                var workbookStylesPart = document.WorkbookPart.WorkbookStylesPart;
                var stylesheet = workbookStylesPart != null ? workbookStylesPart.Stylesheet : null;
                var cellFormats = stylesheet != null ? stylesheet.CellFormats : null;

                string workSheetsId = workbook.Descendants<Sheet>().First(s => s.Name == workSheetName).Id;
                var worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(workSheetsId);
                var resultado = GetRows(worksheetPart, sharedStringTable, cellFormats);
                return resultado;
            }
        }

        /// <summary>
        /// obtiene todas las hojas y su contenido de un documento excel
        /// </summary>
        /// <param name="index">indice de la hoja a obtener 1..n</param>
        /// <returns>List : lista de filas que contiene la hoja</returns>
        public IEnumerable<RowData> ReadWorkSheet(int index)
        {
            using (var document = OpenDocument())
            {
                var workBookPart = document.WorkbookPart;
                var workbook = workBookPart.Workbook;
                var sharedStringTablePart = workBookPart.SharedStringTablePart;
                var sharedStringTable = sharedStringTablePart != null ? sharedStringTablePart.SharedStringTable : null;
                var workbookStylesPart = document.WorkbookPart.WorkbookStylesPart;
                var stylesheet = workbookStylesPart != null ? workbookStylesPart.Stylesheet : null;
                var cellFormats = stylesheet != null ? stylesheet.CellFormats : null;

                string workSheetId = "";
                if (index == 1)
                {
                    workSheetId = workbook.Descendants<Sheet>().First().Id;
                }
                else
                {
                    var workSheets = from work in workbook.Descendants<Sheet>() select new WorkSheet() { Id = work.Id };
                    int getIndex = 1;
                    foreach (var sheet in workSheets)
                    {
                        if (getIndex == index)
                        {
                            workSheetId = sheet.Id;
                            break;
                        }
                        getIndex++;
                    }
                }
                var worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(workSheetId);
                var resultado = GetRows(worksheetPart, sharedStringTable, cellFormats);
                return resultado;
            }
        }
        /// <summary>
        /// Permite obtener las filas de una hoja excel
        /// </summary>
        /// <param name="worksheetPart">Hoja excel OPEN XML</param>
        /// <param name="sharedStringTable">Tabla de valores compartidos</param>
        /// <param name="cellFormats">Formato de celdas</param>
        /// <returns>Lista de filas</returns>
        private IEnumerable<RowData> GetRows(WorksheetPart worksheetPart, SharedStringTable sharedStringTable, CellFormats cellFormats)
        {
            int rowIni = this.IgnoreHeaderRow ? 2 : 0;
            var resultado = from row in worksheetPart.Worksheet.Descendants<Row>()
                            where row.RowIndex >= rowIni
                            select new RowData(row, sharedStringTable, cellFormats);
            return resultado;
        }

        /// <summary>
        /// Guarda el documento en una ruta fisica
        /// </summary>
        /// <param name="workSheets">hoja que incluira el archivo a guardar</param>
        public void SaveNewDocument(List<WorkSheet> workSheets)
        {
            FileStream fs = File.OpenWrite(this.Filepath);
            byte[] ms = this.CreateNewDocument(workSheets);
            fs.Write(ms, 0, ms.Length);
            fs.Close();
        }

        /// <summary>
        /// Guarda el documento en una ruta fisica
        /// </summary>
        /// <param name="workSheet">hoja que incluira el archivo a guardar</param>
        public void SaveNewDocument(WorkSheet workSheet)
        {
            FileStream fs = File.OpenWrite(this.Filepath);
            byte[] ms = this.CreateNewDocument(workSheet);
            fs.Write(ms, 0, ms.Length);
            fs.Close();
        }

        /// <summary>
        /// Guarda el documento en una ruta fisica
        /// </summary>
        /// <param name="memoryStreamAsByte">bytes del contenido del documento</param>
        public void SaveNewDocument(byte[] memoryStreamAsByte)
        {
            FileStream fs = File.OpenWrite(this.Filepath);
            fs.Write(memoryStreamAsByte, 0, memoryStreamAsByte.Length);
            fs.Close();
        }

        /// <summary>
        /// crea un documento nuevo como bytes para ser retornados como
        /// File a la vista
        /// </summary>
        /// <param name="data">datos del archivo a generar</param>
        /// <param name="cabecera">cabecera del archivo a generar</param>
        /// <returns>bytes del documento</returns>
        public byte[] CreateNewDocument(List<List<string>> data, List<string> cabecera = null)
        {

            WorkSheet hoja = new WorkSheet();
            hoja.Rows = new List<RowData>();
            if (cabecera != null)
            {
                RowData headerRow = new RowData();
                headerRow.SetValues(cabecera.ToArray());
                hoja.HeaderRow = headerRow;
            }
            if (data != null)
            {
                hoja.setDataString(data);
            }
            return CreateNewDocument(hoja);
        }

        /// <summary>
        /// crea un documento nuevo como bytes para ser retornados como
        /// File a la vista
        /// </summary>
        /// <param name="workSheet">hoja que incluira el archivo</param>
        /// <returns>bytes del documento</returns>
        public byte[] CreateNewDocument(WorkSheet workSheet)
        {
            return CreateNewDocument(new List<WorkSheet> { workSheet });
        }

        /// <summary>
        /// crea un documento nuevo como bytes para ser retornados como bytes a la vista
        /// </summary>
        /// <param name="workSheets"> Lista de hojas que incluira el archivo</param>
        /// <returns>bytes del documento</returns>
        public byte[] CreateNewDocument(List<WorkSheet> workSheets)
        {
            var memoryStream = new MemoryStream();

            AddDefaultFormat();

            using (var excel = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook, true))
            {
                FileVersion fv = new FileVersion();
                fv.ApplicationName = "Microsoft Office Excel";
                WorkbookPart wbp = excel.AddWorkbookPart();
                wbp.AddNewPart<SharedStringTablePart>();
                this.stringItemIndexMap = new Hashtable();
                if (wbp.SharedStringTablePart.SharedStringTable == null)
                {
                    wbp.SharedStringTablePart.SharedStringTable = new SharedStringTable();
                    nextIndexSharedString = 0;
                }

                Workbook wb = new Workbook();
                DefinedNames definedNamesCol = new DefinedNames();

                if (this.Styles != null && this.Styles.Count > 0)
                {
                    WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();
                    wbsp.Stylesheet = CreateStylesheet();
                    wbsp.Stylesheet.Save();
                }

                Sheets sheets = new Sheets();

                UInt32 indexSheet = 1;

                foreach (var workSheet in workSheets)
                {
                    if (workSheet.DefinedNameValues != null)
                    {
                        foreach (var definedNameValue in workSheet.DefinedNameValues)
                        {
                            DefinedName definedName = new DefinedName()
                            {
                                Name = definedNameValue.Key,
                                Text = definedNameValue.SheetName + "!$" + definedNameValue.StartColumn + "$" + definedNameValue.StartRow + ":$" + definedNameValue.StartColumn + "$" + definedNameValue.EndRow
                            };
                            definedNamesCol.Append(definedName);
                        }
                    }

                    MergeCells mergeCells = new MergeCells();
                    bool existeMerge = CheckUnionOfCells(workSheet);

                    workSheet.Id = "rId" + indexSheet;
                    WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>(workSheet.Id);
                    wsp.Worksheet = GenerateWorksheetContent(workSheet, wbp);

                    if (existeMerge)
                    {
                        mergeCells = MergeCells(workSheet);
                        wsp.Worksheet.InsertAfter(mergeCells, wsp.Worksheet.Elements<SheetData>().First());
                    }

                    wsp.Worksheet.Save();

                    Sheet sheet = new Sheet();
                    sheet.Name = workSheet.Name == null || workSheet.Name.Equals("") ? "Sheet" + indexSheet : workSheet.Name;
                    sheet.SheetId = indexSheet++;
                    sheet.Id = wbp.GetIdOfPart(wsp);
                    sheets.Append(sheet);

                }
                wb.Append(fv);
                wb.Append(sheets);

                if (definedNamesCol.Count() > 0)
                {
                    wb.Append(definedNamesCol);
                }

                excel.WorkbookPart.Workbook = wb;
                excel.WorkbookPart.SharedStringTablePart.SharedStringTable.Save();
                excel.WorkbookPart.Workbook.Save();

                //excel.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;
                excel.Close();
            }

            var result = memoryStream.ToArray();
            memoryStream.Close();
            memoryStream.Dispose();

            return result;
        }
        /// <summary>
        /// crea un documento nuevo como bytes para ser retornados como bytes a la vista
        /// </summary>
        /// <param name="pathInputFile">Ruta</param>
        /// <param name="listWorkSheet">Lista de hojas</param>
        /// <returns>Bytes del archivo</returns>
        public byte[] CreateNewDocument(string pathInputFile, List<WorkSheet> listWorkSheet)
        {
            MemoryStream memoryStream = new MemoryStream();

            FileStream fileStream = new FileStream(pathInputFile, FileMode.Open);

            AddDefaultFormat();

            fileStream.CopyTo(memoryStream, 4096);
            fileStream.Close();
            fileStream.Dispose();

            //Open up the copied template workbook
            using (SpreadsheetDocument excel = SpreadsheetDocument.Open(memoryStream, true))
            {
                //Access the main Workbook part, which contains all references
                WorkbookPart workbookPart = excel.WorkbookPart;

                workbookPart.AddNewPart<SharedStringTablePart>();
                this.stringItemIndexMap = new Hashtable();
                if (workbookPart.SharedStringTablePart.SharedStringTable == null)
                {
                    workbookPart.SharedStringTablePart.SharedStringTable = new SharedStringTable();
                    nextIndexSharedString = 0;
                }

                foreach (var workSheet in listWorkSheet)
                {
                    string workSheetId = workbookPart.Workbook.Descendants<Sheet>().First().Id;

                    //Grab the first worksheet
                    WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(workSheetId);

                    //SheetData will contain all the data 
                    SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

                    foreach (var row in workSheet.Rows)
                    {
                        sheetData.AppendChild(CreateBody(row, workbookPart));
                    }

                    worksheetPart.Worksheet.Save();
                }

                excel.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;
                excel.WorkbookPart.SharedStringTablePart.SharedStringTable.Save();

                excel.Close();
            }

            var result = memoryStream.ToArray();
            memoryStream.Close();
            memoryStream.Dispose();

            return result;

        }

        /// <summary>
        /// crea un documento nuevo en bytes para ser retornados como File a la vista
        /// </summary>
        /// <param name="listObject">Lista de objetos que se incluiran como registros al excel</param>
        /// <param name="mapper">mapeo de listaPropertiesColumn con atributos</param>
        /// <returns>bytes del documento</returns>
        public byte[] CreateNewDocument(List<object> listObject, Dictionary<int, string> mapper)
        {
            WorkSheet workSheet = new WorkSheet();
            workSheet.MapperListToRows(listObject, mapper);
            return this.CreateNewDocument(workSheet);
        }

        /// <summary>
        /// Crea un nuevo documento del tipo excel
        /// </summary>        
        /// <param name="listObject">Lista de objetos que se incluiran como registros al excel</param>
        /// <param name="mapper">Mapeo de listaPropertiesColumn con atributos</param>
        /// <param name="header">Cabecera del detalle</param>
        /// <returns>Bytes del documento</returns>
        public byte[] CreateNewDocument(List<object> listObject, Dictionary<int, string> mapper, RowData header)
        {
            WorkSheet workSheet = new WorkSheet();
            workSheet.HeaderRow = header;
            workSheet.MapperListToRows(listObject, mapper);
            return this.CreateNewDocument(workSheet);
        }

        /// <summary>
        /// Crea un nuevo documento del tipo excel
        /// </summary>        
        /// <param name="listObject">Lista de objetos que se incluiran como registros al excel</param>
        /// <param name="mapper">Mapeo de listaPropertiesColumn con atributos</param>
        /// <param name="header">Cabecera del detalle</param>
        /// <param name="agrupador">Propiedad que indicará si una columna es agrupada</param>
        /// <returns>Bytes del documento</returns>
        public byte[] CreateNewDocument(List<object> listObject, Dictionary<int, string> mapper, RowData header, String agrupador)
        {
            WorkSheet workSheet = new WorkSheet();
            workSheet.HeaderRow = header;
            workSheet.MapperListToRows(listObject, mapper, agrupador);
            return this.CreateNewDocument(workSheet);
        }
        /// <summary>
        /// Permite crear los formatos por defectos
        /// </summary>
        private void AddDefaultFormat()
        {
            NumberFormat formatosNumerico = new NumberFormat();

            if (numberingFormats == null)
            {
                numberingFormats = formatosNumerico.numberingFormats;

                if (Styles == null) { Styles = new List<Style>(); }

                var styleId = this.Styles.FirstOrDefault(s => s.Id.Equals("DefaultText"));

                if (styleId == null)
                {
                    Styles.Add(new Style { Id = "DefaultText", BackGroundColor = "", FontName = "Calibri", FontSize = 8, Border = false, NumericFormat = formatosNumerico.ForcedTextFormat.NumberFormatId });
                }
            }
            else { Styles.Add(new Style { Id = "DefaultText", BackGroundColor = "", FontName = "Calibri", FontSize = 8, Border = false, NumericFormat = formatosNumerico.ForcedTextFormat.NumberFormatId }); }


            //Styles.Add(new Style { Id = "detalleCabecera", BackGroundColor = "e8f2fb", Color = "618AAE", FontName = "Calibri", FontSize = 11, Border = true, FontBold = true, NumericFormat = formatosNumerico.ForcedTextFormat.NumberFormatId });
            //Styles.Add(new Style { Id = "detalleGrupo", BackGroundColor = "F5F6FA", Color = "000000", FontName = "Calibri", FontSize = 10, Border = true, FontBold = true, NumericFormat = formatosNumerico.ForcedTextFormat.NumberFormatId });
            //Styles.Add(new Style { Id = "detalleDatos", BackGroundColor = "FFFFFF", Color = "000000", FontName = "Calibri", FontSize = 8, Border = true, FontBold = false, NumericFormat = formatosNumerico.ForcedTextFormat.NumberFormatId });
        }
        /// <summary>
        /// Permite revisar si la celda esta unida
        /// </summary>
        /// <param name="workSheet">Hoja de excel</param>
        /// <returns>Estado de la validación</returns>
        private bool CheckUnionOfCells(WorkSheet workSheet)
        {
            bool option = false;

            if (workSheet.Rows != null)
            {
                foreach (var row in workSheet.Rows)
                {
                    option = SearchUnitedCells(row.Cells);

                    if (option)
                    {
                        break;
                    }
                }
            }

            return option;
        }
        /// <summary>
        /// Permite buscar celdas unificadas
        /// </summary>
        /// <param name="cells">Lista de celdas</param>
        /// <returns>Estado de la validación</returns>
        private bool SearchUnitedCells(List<CellData> cells)
        {
            bool option = false;

            foreach (var cell in cells)
            {
                if (cell.CellsRange != "")
                {
                    option = true;
                    break;
                }
            }

            return option;
        }
        /// <summary>
        /// Permite obtener celdas unificadas
        /// </summary>
        /// <param name="workSheet">Hoja excel</param>
        /// <returns>Celdas unidas</returns>
        private MergeCells MergeCells(WorkSheet workSheet)
        {
            MergeCells mergeCells;
            mergeCells = new MergeCells();

            if (workSheet.Rows != null)
            {
                foreach (var row in workSheet.Rows)
                {
                    foreach (var celda in row.Cells)
                    {
                        if (celda.CellsRange != "")
                        {
                            MergeCell mergeCell = new MergeCell() { Reference = new StringValue(celda.CellsRange) };
                            mergeCells.Append(mergeCell);
                        }
                    }
                }
            }

            return mergeCells;
        }

        /// <summary>
        /// implemente el contenido del nuevo documento Excel
        /// </summary>
        /// <param name="workSheet"> hoja de la que se leera el contenido</param>
        /// <param name="wbp">Hoja</param>
        /// <returns>Retorna una hoja OPEN XML</returns>
        private Worksheet GenerateWorksheetContent(WorkSheet workSheet, WorkbookPart wbp)
        {
            Worksheet worksheet = new Worksheet();
            SheetData sheetData = new SheetData();

            UInt32 rowIndex = 1;
            if (workSheet.Title != null && !workSheet.Title.Equals(""))
            {
                sheetData.Append(CreateTitle(workSheet.Title, wbp));
                rowIndex++;
            }
            if (workSheet.HeaderRow != null)
            {
                sheetData.Append(CreateHeader(workSheet.HeaderRow, rowIndex, wbp));
                rowIndex++;
            }

            if (workSheet.Rows != null)
            {

                foreach (var row in workSheet.Rows)
                {

                    sheetData.Append(CreateBody(row, wbp));
                    rowIndex++;
                }
            }

            if (workSheet.Columns != null && workSheet.Columns.Count() > 0)
            {
                worksheet.Append(workSheet.Columns);
            }

            worksheet.Append(sheetData);

            return worksheet;
        }
        /// <summary>
        /// Permite crear la fila de titulo
        /// </summary>
        /// <param name="title">Titlo</param>
        /// <param name="wbp">Hoja</param>
        /// <returns>Título de Fila</returns>
        private Row CreateTitle(string title, WorkbookPart wbp)
        {
            var titleRow = new Row { RowIndex = 1 };
            Cell newCell = new Cell { CellReference = "A1", CellValue = new CellValue(title), DataType = CellValues.String };
            titleRow.Append(newCell);

            if (newCell.DataType == CellValues.String)
            {
                AddSharedString(title, wbp);
            }

            return titleRow;
        }
        /// <summary>
        /// Permite crear la fila de cabeceras.
        /// </summary>
        /// <param name="headerRow">Datos de la cabecera</param>
        /// <param name="rowIndex">Indice de la fila</param>
        /// <param name="wbp">Hoja</param>
        /// <returns>Fila de cabecera</returns>
        private Row CreateHeader(RowData headerRow, UInt32 rowIndex, WorkbookPart wbp)
        {
            var newRow = new Row { RowIndex = rowIndex };
            foreach (var cell in headerRow.Cells)
            {
                float numValue;
                Cell newCell = new Cell { CellReference = cell.CellReference, CellValue = new CellValue(cell.Value), DataType = float.TryParse(cell.Value, out numValue) ? CellValues.Number : CellValues.String };
                if (this.Styles != null && this.Styles.Count > 0)
                {
                    var styleId = this.Styles.FirstOrDefault(s => s.Id.Equals((cell.StyleId == null || cell.StyleId == "") ? headerRow.DefaultStyleId : cell.StyleId));
                    if (styleId != null)
                    {
                        newCell.StyleIndex = (uint)styleId.StyleIndex;
                    }
                }
                newRow.Append(newCell);

                //
                if (newCell.DataType == CellValues.String)
                {
                    AddSharedString(cell.Value, wbp);
                }
                //

            }
            return newRow;
        }
        /// <summary>
        /// Permite agregar valores a la tabla de valores compartidas
        /// </summary>
        /// <param name="value">Valor</param>
        /// <param name="wbp">Hoja</param>
        private void AddSharedString(dynamic value, WorkbookPart wbp)
        {
            if (value != null)
            {
                var index = stringItemIndexMap[value] as string;

                if (index == null)
                {
                    wbp.SharedStringTablePart.SharedStringTable.AppendChild(new SharedStringItem(new Text(value)));

                    index = nextIndexSharedString.ToString();
                    stringItemIndexMap.Add(value, index);

                    nextIndexSharedString++;
                }
            }
        }
        /// <summary>
        /// Permite obtener una celda formateada
        /// </summary>
        /// <param name="cell">Celda de datos</param>
        /// <returns>Celda generada</returns>
        private Cell ReturnFormattedCell(CellData cell)
        {
            float numValue;
            Cell newCell = new Cell { CellFormula = cell.CellFormula, CellReference = cell.CellReference, CellValue = new CellValue(cell.Value) };

            if (cell.DataType.Trim() == "System.String")
                newCell.DataType = CellValues.String;
            else
                newCell.DataType = float.TryParse(cell.Value, out numValue) ? CellValues.Number : CellValues.String;

            return newCell;
        }
        /// <summary>
        /// Permite generar fila del cuerpo de hoja
        /// </summary>
        /// <param name="rowData">Fila de datos OPEN XML</param>
        /// <param name="wbp">Hoja OPEN XML</param>
        /// <returns>Fila generada</returns>
        private Row CreateBody(RowData rowData, WorkbookPart wbp)
        {
            var newRow = new Row { RowIndex = rowData.RowIndex, OutlineLevel = rowData.OutlineLevel };

            foreach (var cell in rowData.Cells)
            {
                //float numValue;
                //Cell newCell = new Cell { CellFormula = cell.CellFormula, CellReference = cell.CellReference, CellValue = new CellValue(cell.Value), DataType = float.TryParse(cell.Value, out numValue) ? CellValues.Number : CellValues.String  };
                Cell newCell = ReturnFormattedCell(cell);
                if (this.Styles != null && this.Styles.Count > 0)
                {
                    var styleId = this.Styles.FirstOrDefault(s => s.Id.Equals((cell.StyleId == null || cell.StyleId == "") ? rowData.DefaultStyleId : cell.StyleId));
                    if (styleId != null)
                    {
                        newCell.StyleIndex = (uint)styleId.StyleIndex;
                    }
                    else
                    {
                        if (cell.DataType == "System.String")
                        {
                            var estilo = this.Styles.FirstOrDefault(s => s.Id.Equals("DefaultText"));
                            newCell.StyleIndex = (uint)estilo.StyleIndex;
                        }
                    }
                }

                if (newCell.DataType == CellValues.String)
                {
                    AddSharedString(cell.Value, wbp);
                }

                newRow.Append(newCell);
            }

            return newRow;
        }

        /// <summary>
        /// crea la lista de listaEstilo que manejara el excel
        /// </summary>
        /// <returns>Stylesheet</returns>
        private Stylesheet CreateStylesheet()
        {
            Stylesheet styleSheet = new Stylesheet();

            Borders borders = CreateBorder();
            CellStyleFormats cellStyleFormat = CreateCellStyleFormats();
            CellStyles cellStyles = CellStyles();

            Fonts fonts = new Fonts();
            Fills fills = new Fills();
            CellFormats cellFormats = new CellFormats();

            GetFont(ref fonts, "Calibri", 8, false, "000000");
            GetFill(ref fills, "");
            PatternFill patternFill = new PatternFill { PatternType = PatternValues.Gray125 };
            Fill fill = new Fill { PatternFill = patternFill };
            fills.Append(fill);

            cellFormats.Append(CreateCellFormat());

            int styleIndex = 1;
            foreach (Style style in this.Styles)
            {
                CellFormat cellformat = CreateCellFormat(style, ref fonts, ref fills);

                Alignment alignment = new Alignment { WrapText = style.WrapText };
                if (style.HorizontalAlignment != 0)
                {
                    switch ((int)style.HorizontalAlignment)
                    {
                        case 1: alignment.Horizontal = HorizontalAlignmentValues.Left; break;
                        case 2: alignment.Horizontal = HorizontalAlignmentValues.Center; break;
                        case 3: alignment.Horizontal = HorizontalAlignmentValues.Right; break;
                    }
                }

                if (style.VerticalAlignmentAlignment != 0)
                {
                    switch ((int)style.VerticalAlignmentAlignment)
                    {
                        case 0: alignment.Vertical = VerticalAlignmentValues.Top; break;
                        case 1: alignment.Vertical = VerticalAlignmentValues.Center; break;
                        case 2: alignment.Vertical = VerticalAlignmentValues.Bottom; break;
                        case 3: alignment.Vertical = VerticalAlignmentValues.Justify; break;
                        case 4: alignment.Vertical = VerticalAlignmentValues.Distributed; break;
                    }
                }

                cellformat.AppendChild(alignment);
                cellFormats.Append(cellformat);

                style.StyleIndex = styleIndex++;
                style.FontId = cellformat.FontId;
                style.FillId = cellformat.FillId;
            }

            fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
            fonts.Count = UInt32Value.FromUInt32((uint)fonts.ChildElements.Count);
            cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);

            styleSheet.Append(fonts);
            styleSheet.Append(fills);
            styleSheet.Append(borders);
            styleSheet.NumberingFormats = numberingFormats;
            styleSheet.Append(cellStyleFormat);
            styleSheet.Append(cellFormats);
            styleSheet.Append(cellStyles);
            styleSheet.Append(new DifferentialFormats { Count = 0 });
            styleSheet.Append(new TableStyles
            {
                Count = 0,
                DefaultTableStyle = StringValue.FromString("TableStyleMedium9"),
                DefaultPivotStyle = StringValue.FromString("PivotStyleLight16")
            });

            return styleSheet;
        }

        /// <summary>
        /// Captura el Fondo
        /// </summary>
        /// <param name="fonts">Fondo</param>
        /// <param name="name">Nombre</param>
        /// <param name="size">Tamaño</param>
        /// <param name="bold">Grado</param>
        /// <param name="color">Color</param>
        /// <returns></returns>
        private uint GetFont(ref Fonts fonts, string name, int size, bool bold, string color)
        {
            var fontStyle = this.Styles.FirstOrDefault(s => s.FontName != null && s.FontName.Equals(name) &&
                                                          s.FontSize == size &&
                                                          s.Color != null && s.Color.Equals(color) &&
                                                          s.FontBold == bold);
            uint fontId;
            if (fontStyle == null || fontStyle.FontId == 0)
            {
                Font font = new Font();
                FontName fontName = new FontName { Val = StringValue.FromString(name == null || name.Equals("") ? "arial" : name) };
                FontSize fontSize = new FontSize { Val = DoubleValue.FromDouble(size == 0 ? 11 : size) };
                Bold fontBold = new Bold { Val = bold };
                Color fontColor = new Color
                {
                    Rgb =
                        HexBinaryValue.FromString(color == null || color.Equals("")
                                                      ? "000000"
                                                      : color)
                };
                font.Bold = fontBold;
                font.Color = fontColor;
                font.FontName = fontName;
                font.FontSize = fontSize;
                fonts.Append(font);
                fontId = (uint)fonts.ChildElements.Count - 1;
            }
            else
            {
                fontId = fontStyle.FontId;
            }
            return fontId;
        }

        /// <summary>
        /// Captura Rellenos
        /// </summary>
        /// <param name="fills">Rellenos</param>
        /// <param name="color">Color</param>
        /// <returns>Rellenos de Estilos</returns>
        private uint GetFill(ref Fills fills, string color)
        {
            var fillStyle = this.Styles.FirstOrDefault(s => s.BackGroundColor.Equals(color));
            if (fillStyle == null || fillStyle.FillId == 0)
            {
                Fill fill = new Fill();
                PatternFill patternFill = new PatternFill();
                if (color == null || color.Equals("") || color.Equals("none"))
                {
                    patternFill.PatternType = PatternValues.None;
                }
                else
                {
                    patternFill.PatternType = PatternValues.Solid;
                    patternFill.ForegroundColor = new ForegroundColor();
                    patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString(color);
                    patternFill.BackgroundColor = new BackgroundColor();
                    patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
                }
                fill.PatternFill = patternFill;
                fills.Append(fill);
                return (uint)fills.ChildElements.Count - 1;
            }
            return fillStyle.FillId;
        }

        /// <summary>
        /// Creación de Bordes
        /// </summary>
        /// <returns></returns>
        private Borders CreateBorder()
        {
            Borders borders = new Borders();

            Border border = new Border();
            border.LeftBorder = new LeftBorder();
            border.RightBorder = new RightBorder();
            border.TopBorder = new TopBorder();
            border.BottomBorder = new BottomBorder();
            border.DiagonalBorder = new DiagonalBorder();
            borders.Append(border);

            border = new Border();
            border.LeftBorder = new LeftBorder();//new Color() { Auto = true });
            border.LeftBorder.Style = BorderStyleValues.Thin;
            border.RightBorder = new RightBorder();//new Color() { Auto = true });
            border.RightBorder.Style = BorderStyleValues.Thin;
            border.TopBorder = new TopBorder();//new Color() { Auto = true });
            border.TopBorder.Style = BorderStyleValues.Thin;
            border.BottomBorder = new BottomBorder();//new Color() { Auto = true });
            border.BottomBorder.Style = BorderStyleValues.Thin;
            border.DiagonalBorder = new DiagonalBorder();
            borders.Append(border);
            borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
            return borders;
        }

        /// <summary>
        /// Crear formatos de estilo Celda
        /// </summary>
        /// <returns>Formato de Celda</returns>
        private CellStyleFormats CreateCellStyleFormats()
        {
            CellStyleFormats cellStyleFormat = new CellStyleFormats();
            cellStyleFormat.Append(CreateCellFormat());
            cellStyleFormat.Count = UInt32Value.FromUInt32((uint)cellStyleFormat.ChildElements.Count);
            return cellStyleFormat;
        }

        /// <summary>
        /// Estilo de Celda
        /// </summary>
        /// <returns>Stilo de Celda</returns>
        private CellStyles CellStyles()
        {
            CellStyles cellStyles = new CellStyles();
            cellStyles.Append(new CellStyle { Name = StringValue.FromString("Normal"), FormatId = 0, BuiltinId = 0 });
            cellStyles.Count = UInt32Value.FromUInt32((uint)cellStyles.ChildElements.Count);
            return cellStyles;
        }

        /// <summary>
        /// Crea Tipo de Formato de celda
        /// </summary>
        /// <returns>Formato de celda</returns>
        private CellFormat CreateCellFormat()
        {
            var cellformat = new CellFormat { NumberFormatId = 0, FormatId = 0, BorderId = 0, FontId = 0, FillId = 0 };
            return cellformat;
        }

        /// <summary>
        /// Crea formato de celda
        /// </summary>
        /// <param name="style">Estilos</param>
        /// <param name="fonts">Fondos</param>
        /// <param name="fills">Rellenos</param>
        /// <returns>Formato de celda</returns>
        private CellFormat CreateCellFormat(Style style, ref Fonts fonts, ref Fills fills)
        {
            var cellformat = new CellFormat
            {
                NumberFormatId = 0,
                FormatId = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true),
                BorderId = (uint)(style.Border ? 1 : 0),
                FontId = GetFont(ref fonts, style.FontName, style.FontSize, style.FontBold, style.Color),
                FillId = GetFill(ref fills, style.BackGroundColor),

            };

            if (style.NumericFormat != null)
            {
                cellformat.NumberFormatId = style.NumericFormat;
                cellformat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            }

            //cellformat.BorderId = 1;
            //cellformat.ApplyBorder = true;

            return cellformat;
        }

        /// <summary>
        /// Permite Crear las columnas para la información
        /// </summary>
        /// <param name="startColumnIndex">Indicador de Inicio de la Columna</param>
        /// <param name="endColumnIndex">Indicador de Fin de la Columna</param>
        /// <param name="bestFit">Ajuste</param>
        /// <param name="columnWidth">Tamaño de Columna</param>
        /// <returns>Columnas</returns>
        public Column CreateColumnData(UInt32 startColumnIndex, UInt32 endColumnIndex, BooleanValue bestFit, double? columnWidth = 8)
        {
            Column column;
            column = new Column();

            column.Min = startColumnIndex;
            column.Max = endColumnIndex;
            if (columnWidth.HasValue)
            {
                column.Width = columnWidth;
                column.CustomWidth = true;
            }
            column.BestFit = bestFit;
            return column;
        }

    }
}
